package zy.dao.base.depot.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.base.depot.DepotDAO;
import zy.entity.base.depot.T_Base_Depot;
import zy.entity.sys.set.T_Sys_Set;
import zy.util.CommonUtil;
import zy.util.StringUtil;

@Repository
public class DepotDAOImpl extends BaseDaoImpl implements DepotDAO{

	@Override
	public Integer count(Map<String, Object> params) {
		Object name = params.get("searchContent");
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);//登录人的类型ID
		Object dp_shop_code = params.get("dp_shop_code");//页面传递的店铺编号
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT count(1)");
		sql.append(" FROM t_base_depot t");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(" JOIN t_base_shop sp ON sp_code = dp_shop_code AND sp.companyid = t.companyid");
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
			if(dp_shop_code != null && !"".equals(dp_shop_code)){
				sql.append(" AND dp_shop_code = :dp_shop_code");
			}
		}else{//加盟店
			sql.append(" WHERE 1 = 1");
			sql.append(" AND dp_shop_code = :shop_code");
		}
		if(null != name && !"".equals(name)){
        	sql.append(" AND (INSTR(dp_code,:name)>0 OR INSTR(dp_spell,:name)>0 OR INSTR(dp_name,:name)>0)");
        }
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public List<T_Base_Depot> list(Map<String, Object> params) {
		Object name = params.get("searchContent");
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);//登录人的类型ID
		Object dp_shop_code = params.get("dp_shop_code");//页面传递的店铺编号
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT dp_id,dp_code,dp_name,dp_shop_code,dp_default,dp_man,dp_tel,dp_mobile,t.companyid,sp_name AS shop_name,ty_name");
		sql.append(" FROM t_base_depot t");
		sql.append(" JOIN t_base_shop sp ON sp_code = dp_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
			if(dp_shop_code != null && !"".equals(dp_shop_code)){
				sql.append(" AND dp_shop_code = :dp_shop_code");
			}
		}else{//加盟店
			sql.append(" JOIN common_type on ty_id=sp_shop_type");
			sql.append(" WHERE 1 = 1");
			sql.append(" AND dp_shop_code = :shop_code");
		}
		if(null != name && !"".equals(name)){
        	sql.append(" AND (INSTR(dp_code,:name)>0 OR INSTR(dp_spell,:name)>0 OR INSTR(dp_name,:name)>0)");
        }
		
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY dp_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Base_Depot.class));
	}
	
	@Override
	public List<T_Base_Depot> list4buy(Map<String, Object> params) {
		Object searchContent = params.get("searchContent");
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);//登录人的类型ID
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT dp_id,dp_code,dp_name,dp_shop_code,dp_default,dp_man,dp_tel,dp_mobile,t.companyid,sp_name AS shop_name,ty_name");
		sql.append(" FROM t_base_depot t");
		sql.append(" JOIN t_base_shop sp ON sp_code = dp_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{
			sql.append(" JOIN common_type on ty_id=sp_shop_type");
			sql.append(" WHERE 1 = 1");
			sql.append(" AND dp_shop_code = :shop_code");
		}
		if(StringUtil.isNotEmpty(searchContent)){
        	sql.append(" AND (INSTR(dp_code,:searchContent)>0 OR INSTR(dp_spell,:searchContent)>0 OR INSTR(dp_name,:searchContent)>0)");
        }
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY dp_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Base_Depot.class));
	}
	
	@Override
	public List<T_Base_Depot> list4batch(Map<String, Object> params) {
		Object searchContent = params.get("searchContent");
		T_Sys_Set sysSet = (T_Sys_Set)params.get(CommonUtil.KEY_SYSSET);
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);//登录人的类型ID
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT dp_id,dp_code,dp_name,dp_shop_code,dp_default,dp_man,dp_tel,dp_mobile,t.companyid,sp_name AS shop_name,ty_name");
		sql.append(" FROM t_base_depot t");
		sql.append(" JOIN t_base_shop sp ON sp_code = dp_shop_code AND sp.companyid = t.companyid");
		if (sysSet != null && sysSet.getSt_batch_os() != null && sysSet.getSt_batch_os().intValue() == 1) {// 批发仓库选择自营店及合伙店
			if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
				sql.append(getShopSQL(shop_type, 0));
				sql.append(" WHERE 1 = 1");
			}else{//自营店、合伙店、加盟店
				sql.append(" JOIN common_type on ty_id=sp_shop_type");
				sql.append(" WHERE 1 = 1");
				sql.append(" AND dp_shop_code = :shop_code");
			}
		}else {
			sql.append(" JOIN common_type on ty_id=sp_shop_type");
			sql.append(" WHERE 1 = 1");
			sql.append(" AND dp_shop_code = :shop_code");
		}
		if(StringUtil.isNotEmpty(searchContent)){
        	sql.append(" AND (INSTR(dp_code,:searchContent)>0 OR INSTR(dp_spell,:searchContent)>0 OR INSTR(dp_name,:searchContent)>0)");
        }
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY dp_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Base_Depot.class));
	}
	
	@Override
	public List<T_Base_Depot> list4stock(Map<String, Object> params) {
		Object searchContent = params.get("searchContent");
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);//登录人的类型ID
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT dp_id,dp_code,dp_name,dp_shop_code,dp_default,dp_man,dp_tel,dp_mobile,t.companyid,sp_name AS shop_name,ty_name");
		sql.append(" FROM t_base_depot t");
		sql.append(" JOIN t_base_shop sp ON sp_code = dp_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//加盟店
			sql.append(" JOIN common_type on ty_id=sp_shop_type");
			sql.append(" WHERE 1 = 1");
			sql.append(" AND dp_shop_code = :shop_code");
		}
		if(StringUtil.isNotEmpty(searchContent)){
        	sql.append(" AND (INSTR(dp_code,:searchContent)>0 OR INSTR(dp_spell,:searchContent)>0 OR INSTR(dp_name,:searchContent)>0)");
        }
		if(StringUtil.isNotEmpty(params.get("sp_code"))){
        	sql.append(" AND sp_code = :sp_code");
        }
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY dp_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Base_Depot.class));
	}
	
	@Override
	public List<T_Base_Depot> list4want(Map<String, Object> params) {
		Object searchContent = params.get("searchContent");
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT dp_id,dp_code,dp_name,dp_shop_code,dp_default,dp_man,dp_tel,dp_mobile,t.companyid,sp_name AS shop_name,");
		sql.append(" (SELECT ty_name FROM common_type WHERE ty_id = sp_shop_type LIMIT 1) AS ty_name");
		sql.append(" FROM t_base_depot t");
		sql.append(" JOIN t_base_shop sp ON sp_code = dp_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND dp_shop_code = :shop_code");
		if(StringUtil.isNotEmpty(searchContent)){
        	sql.append(" AND (INSTR(dp_code,:searchContent)>0 OR INSTR(dp_spell,:searchContent)>0 OR INSTR(dp_name,:searchContent)>0)");
        }
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY dp_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Base_Depot.class));
	}
	
	@Override
	public List<T_Base_Depot> list4allot(Map<String, Object> params) {
		Object searchContent = params.get("searchContent");
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT dp_id,dp_code,dp_name,dp_shop_code,dp_default,dp_man,dp_tel,dp_mobile,t.companyid,sp_name AS shop_name,");
		sql.append(" (SELECT ty_name FROM common_type WHERE ty_id = sp_shop_type LIMIT 1) AS ty_name");
		sql.append(" FROM t_base_depot t");
		sql.append(" JOIN t_base_shop sp ON sp_code = dp_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND dp_shop_code = :shop_code");
		if(StringUtil.isNotEmpty(searchContent)){
        	sql.append(" AND (INSTR(dp_code,:searchContent)>0 OR INSTR(dp_spell,:searchContent)>0 OR INSTR(dp_name,:searchContent)>0)");
        }
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY dp_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Base_Depot.class));
	}
	
	@Override
	public List<T_Base_Depot> list4sellallocate(Map<String, Object> params) {
		Object searchContent = params.get("searchContent");
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT dp_id,dp_code,dp_name,dp_shop_code,dp_default,dp_man,dp_tel,dp_mobile,t.companyid,sp_name AS shop_name,");
		sql.append(" (SELECT ty_name FROM common_type WHERE ty_id = sp_shop_type LIMIT 1) AS ty_name");
		sql.append(" FROM t_base_depot t");
		sql.append(" JOIN t_base_shop sp ON sp_code = dp_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND dp_shop_code = :shop_code");
		if(StringUtil.isNotEmpty(searchContent)){
        	sql.append(" AND (INSTR(dp_code,:searchContent)>0 OR INSTR(dp_spell,:searchContent)>0 OR INSTR(dp_name,:searchContent)>0)");
        }
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY dp_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Base_Depot.class));
	}
	
	@Override
	public List<T_Base_Depot> listByShop(String shop_code,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT dp_id,dp_code,dp_name,dp_shop_code");
		sql.append(" FROM t_base_depot t");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND dp_shop_code = :shop_code");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY dp_id DESC");
		return namedParameterJdbcTemplate.query(sql.toString(), new MapSqlParameterSource().addValue("shop_code", shop_code).addValue("companyid", companyid), new BeanPropertyRowMapper<>(T_Base_Depot.class));
	}
	
	@Override
	public List<String> listDepotByShop(String shop_code,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT dp_code ");
		sql.append(" FROM t_base_depot dp");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND dp_shop_code = :shop_code");
		sql.append(" AND dp.companyid = :companyid");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), new MapSqlParameterSource().addValue("shop_code", shop_code).addValue("companyid", companyid), String.class);
	}
	
	@Override
	public T_Base_Depot queryByID(Integer id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select dp_id,");
		sql.append(" dp_code,dp_name,dp_spell,dp_default,");
		sql.append(" dp_state,dp_addr,dp_remark,dp_man,");
		sql.append(" dp_tel,dp_mobile,dp_shop_code");
		sql.append(" from t_base_depot d");
		sql.append(" where d.dp_id=:dp_id");
		try{
			T_Base_Depot data = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("dp_id", id),new BeanPropertyRowMapper<>(T_Base_Depot.class));
			return data;
		}catch(Exception e){
			return null;
		}
	}

	@Override
	public void update(T_Base_Depot model) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_base_depot SET ");
		sql.append("dp_name=:dp_name,dp_spell=:dp_spell,dp_state=:dp_state,");
		sql.append("dp_man=:dp_man,dp_tel=:dp_tel,dp_mobile=:dp_mobile,");
		sql.append("dp_addr=:dp_addr,dp_remark=:dp_remark");
		sql.append(" where dp_id=:dp_id ");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(model));
	}

	@Override
	public void updateDefault(T_Base_Depot model) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_base_depot SET ");
		sql.append(" dp_default=0");
		sql.append(" WHERE dp_shop_code=:dp_shop_code ");
		sql.append(" AND companyid=:companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(model));
		sql.setLength(0);
		sql.append(" UPDATE t_base_depot SET ");
		sql.append(" dp_default=1");
		sql.append(" WHERE dp_id=:dp_id ");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(model));
	}

	@Override
	public void save(T_Base_Depot model) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select f_three_code(max(dp_code+0)) from t_base_depot ");
		sql.append(" where 1=1");
		sql.append(" and companyid=:companyid");
		String dp_code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(model), String.class);
		model.setDp_code(dp_code);
		sql.setLength(0);
		sql.append("INSERT INTO t_base_depot (");
		sql.append("dp_code,dp_name,dp_spell,dp_default,");
		sql.append("dp_state,dp_addr,dp_remark,dp_man,");
		sql.append("dp_tel,dp_mobile,dp_shop_code,companyid");
		sql.append(" )VALUES( ");
		sql.append(":dp_code,:dp_name,:dp_spell,:dp_default,");
		sql.append(":dp_state,:dp_addr,:dp_remark,:dp_man,");
		sql.append(":dp_tel,:dp_mobile,:dp_shop_code,:companyid");
		sql.append(")");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(model));
	}
	public Integer useByCode(T_Base_Depot model){
		StringBuffer sql = new StringBuffer("");
		sql.append(" select sd_id from t_stock_data");
		sql.append(" where sd_dp_code=:dp_code");
		sql.append(" and companyid=:companyid");
		sql.append(" limit 1");
		Integer id = null;
		try{
			id = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(model), Integer.class);
		}catch(Exception e){
		}
		return id;
	}
		
	@Override
	public void del(T_Base_Depot model) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" delete from t_base_depot");
		sql.append(" where dp_id=:dp_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(model));
	}

	@Override
	public List<T_Base_Depot> ownDepot(Map<String, Object> params) {
		Object searchContent = params.get("searchContent");
		Object shop_type = params.get(CommonUtil.SHOP_UPTYPE);//登录人的类型ID
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT dp_id,dp_code,dp_name,sp_name AS shop_name,ty_name");
		sql.append(" FROM t_base_depot t");
		sql.append(" JOIN t_base_shop sp ON sp_code = dp_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getSellShopSQL(shop_type, 1));
			sql.append(" WHERE 1 = 1");
		}else{//加盟店
			sql.append(" WHERE 1 = 1");
			sql.append(" AND dp_shop_code = :shop_code");
		}
		if(StringUtil.isNotEmpty(searchContent)){
        	sql.append(" AND INSTR(dp_spell,:dp_spell)>0");
        }
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Base_Depot.class));
	}

}
